"""
Partner Clinics API routes - Partner Clinic Management
Using raw SQL queries with psycopg2 (NO ORM!)
"""

from flask import Blueprint, request, jsonify
from database.connection import execute_query

bp = Blueprint('clinics', __name__, url_prefix='/api/clinics')

@bp.route('', methods=['GET'])
def get_clinics():
    """
    Get all partner clinics with optional filtering
    GET /api/clinics?keyword=xxx&page=1&pageSize=10
    """
    try:
        keyword = request.args.get('keyword', '').strip()
        page = int(request.args.get('page', 1))
        page_size = int(request.args.get('pageSize', 10))
        
        query = """
            SELECT clinic_id, clinic_name, address, contact_person,
                   contact_phone
            FROM partner_clinics
            WHERE 1=1
        """
        
        params = []
        
        # 关键词搜索（诊所名称或联系人）
        if keyword:
            query += " AND (clinic_name ILIKE %s OR contact_person ILIKE %s)"
            search_pattern = f'%{keyword}%'
            params.extend([search_pattern, search_pattern])
        
        query += " ORDER BY clinic_id ASC"
        
        # 分页
        offset = (page - 1) * page_size
        query += " LIMIT %s OFFSET %s"
        params.extend([page_size, offset])
        
        clinics = execute_query(query, tuple(params) if params else None)
        
        # 获取总数
        count_query = """
            SELECT COUNT(*) as count
            FROM partner_clinics
            WHERE 1=1
        """
        count_params = []
        
        if keyword:
            count_query += " AND (clinic_name ILIKE %s OR contact_person ILIKE %s)"
            search_pattern = f'%{keyword}%'
            count_params.extend([search_pattern, search_pattern])
        
        count_result = execute_query(count_query, tuple(count_params) if count_params else None, fetch_one=True)
        total_count = count_result['count'] if count_result else 0
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': clinics,
            'count': total_count
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/<int:clinic_id>', methods=['GET'])
def get_clinic(clinic_id):
    """
    Get a single clinic by ID with order statistics
    GET /api/clinics/:id
    """
    try:
        query = """
            SELECT pc.*,
                   COUNT(DISTINCT o.order_id) as total_orders,
                   COUNT(DISTINCT o.recipient_id) as total_recipients
            FROM partner_clinics pc
            LEFT JOIN orders o ON pc.clinic_id = o.clinic_id
            WHERE pc.clinic_id = %s
            GROUP BY pc.clinic_id
        """
        
        clinic = execute_query(query, (clinic_id,), fetch_one=True)
        
        if not clinic:
            return jsonify({
                'code': 404,
                'message': 'Clinic not found'
            }), 404
        
        return jsonify({
            'code': 200,
            'data': clinic
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
                'message': str(e)
            }), 500

@bp.route('', methods=['POST'])
def create_clinic():
    """
    Create a new partner clinic
    POST /api/clinics
    """
    try:
        data = request.json
        
        required_fields = ['clinic_name', 'address', 'contact_phone']
        for field in required_fields:
            if field not in data:
                return jsonify({
                    'code': 400,
                    'message': f'Missing required field: {field}'
                }), 400
        
        query = """
            INSERT INTO partner_clinics (clinic_name, address, contact_person,
                                        contact_phone)
            VALUES (%s, %s, %s, %s)
            RETURNING clinic_id, clinic_name
        """
        
        params = (
            data['clinic_name'],
            data['address'],
            data.get('contact_person'),
            data['contact_phone']
        )
        
        result = execute_query(query, params, fetch_one=True)
        
        return jsonify({
            'code': 200,
            'message': 'Clinic created successfully',
            'data': result
        }), 201
        
    except Exception as e:
        return jsonify({
            'code': 500,
                'message': str(e)
            }), 500

@bp.route('/<int:clinic_id>', methods=['PUT'])
def update_clinic(clinic_id):
    """
    Update a partner clinic
    PUT /api/clinics/:id
    """
    try:
        data = request.json
        
        update_fields = []
        params = []
        
        allowed_fields = ['clinic_name', 'address', 'contact_person', 'contact_phone']
        
        for field in allowed_fields:
            if field in data:
                update_fields.append(f"{field} = %s")
                params.append(data[field])
        
        if not update_fields:
            return jsonify({
                'code': 400,
                'message': 'No fields to update'
            }), 400
        
        params.append(clinic_id)
        
        query = f"""
            UPDATE partner_clinics
            SET {', '.join(update_fields)}
            WHERE clinic_id = %s
            RETURNING clinic_id, clinic_name
        """
        
        result = execute_query(query, tuple(params), fetch_one=True)
        
        if not result:
            return jsonify({
                'code': 404,
                'message': 'Clinic not found'
            }), 404
        
        return jsonify({
            'code': 200,
            'message': 'Clinic updated successfully',
            'data': result
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
                'message': str(e)
            }), 500

@bp.route('/<int:clinic_id>/orders', methods=['GET'])
def get_clinic_orders(clinic_id):
    """
    Get all orders for a specific clinic
    GET /api/clinics/:id/orders
    """
    try:
        query = """
            SELECT o.order_id, o.order_date, o.status, o.total_amount,
                   r.recipient_code, r.full_name as recipient_name
            FROM orders o
            JOIN recipients r ON o.recipient_id = r.recipient_id
            WHERE o.clinic_id = %s
            ORDER BY o.order_id ASC
        """
        
        orders = execute_query(query, (clinic_id,))
        
        return jsonify({
            'code': 200,
            'data': orders,
            'count': len(orders)
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
                'message': str(e)
            }), 500

